"""
Case Type   : fullsql
Case Name   : 验证事务insert时锁模块信息
Create At   : 2022/02/09
Owner       : zhao-pengcheng-g
Description :
    1.设置track_stmt_stat_level为'l2,l2'
    2.创建表，插入数据
    3.会话1开启事务,插入数据
    4.会话2对同一张表的数据做INSERT操作
    5.新开会话中查询unique_sql_id
    6.查询lock_wait_count和lock_wait_time大于0
    7.校验lock cont和lock time要么同时为0，要么都>0,在l2，l2设置下
    8.校验lock cont和lock_max_count要么同时为0，要么都>0
    9.校验lock_wait_count和lock_wait_time要么同时为0，要么都>0
    10.校验lock_wait_count和detail的关系，l2模式下
    11.查询details is null和lock_count=0
    12.环境清理
Expect      :
    1.设置成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功,(0 rows)
    8.成功,(0 rows)
    9.成功,(0 rows)
    10.成功,(0 rows)
    11.成功,(0 rows)
    12.环境清理成功
History     :
"""
import time
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.ComThread import ComThread


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0011 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 'table_fullsql_0011'

    def test_fullsql(self):
        text = "---step1:设置track_stmt_stat_level为'l2,l2',expect:成功---"
        self.logger.info(text)
        self.config_level = 'track_stmt_stat_level'
        self.show_level_cmd = f'show {self.config_level};'
        show_level_res = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_res)
        self.level_default = show_level_res.splitlines()[-2].strip()
        if 'l2,l2' != self.level_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f"{self.config_level}='l2,l2'")
            self.assertTrue(result, '参数修改失败')
        check_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(check_level_msg)
        self.common.equal_sql_mdg(check_level_msg, f'{self.config_level}',
                                  'l2,l2', '(1 row)', flag='1')

        text = '---step2:创建表，插入数据，expect:成功---'
        self.logger.info(text)
        sql_cmd = f'''select reset_unique_sql('global', 'all', 1);
            set current_schema to public;
            drop table if exists {self.table_name};
            create table {self.table_name}(c1 INT PRIMARY KEY, c2 INT);
            insert into {self.table_name} \
            values(2, 2) ON DUPLICATE KEY UPDATE NOTHING;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertTrue('reset_unique_sql' in sql_msg.splitlines()[0]
                        and 't' in sql_msg.splitlines()[2]
                        and '(1 row)' in sql_msg.splitlines()[3],
                        '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS and
                      self.constant.INSERT_SUCCESS_MSG,
                      sql_msg, '执行失败:' + text)

        text_3 = '---step3:会话1开启事务,更新表数据,expect:成功---'
        self.logger.info(text_3)
        insert_cmd = f'''start transaction;
                         insert into {self.table_name} values (2, 2) \
                         on duplicate key update c2 = values(c2);
                         select pg_sleep(100);
                         end;'''
        session1 = ComThread(self.sh_primy.execut_db_sql, args=(insert_cmd,))
        session1.setDaemon(True)

        text_4 = '---step4:会话2对同一张表的数据做insert操作,expect:成功---'
        self.logger.info(text_4)
        insert_cmd_4 = f'''select pg_sleep(5);
            insert into {self.table_name} values(1, 100),(2, 200) 
            on duplicate key update c2 = excluded.c2;'''
        session2 = ComThread(self.sh_primy.execut_db_sql, args=(insert_cmd_4,))
        session2.setDaemon(True)

        session1.start()
        session2.start()
        session1.join(120)
        session2.join(120)
        result1 = session1.get_result()
        self.logger.info(result1)
        result2 = session2.get_result()
        self.logger.info(result2)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG
                      and self.constant.INSERT_SUCCESS_MSG
                      and self.constant.COMMIT_SUCCESS_MSG,
                      result1, '执行失败:' + text_3)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      result2, '执行失败:' + text_4)

        text = '---step5:新开会话中查询unique_sql_id,expect:成功---'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(60);
            select unique_sql_id from dbe_perf.statement \
            where query like '%duplicate key update c2 = excluded.c2%';'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertNotIn(self.constant.TPCC_ERROR, sql_msg, '执行失败:' + text)
        unique_sql_id = sql_msg.splitlines()[-2].strip()
        self.assertTrue('unique_sql_id' in sql_msg.splitlines()[-4]
                        and '(1 row)' in sql_msg.splitlines()[-1],
                        '执行失败:' + text)

        text = '--step6:查询lock_wait_count和lock_wait_time大于0,expect:成功--'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(30);
            select lock_wait_count>0,lock_wait_time>0 \
            from dbe_perf.get_global_full_sql_by_timestamp\
                (now() - interval'30 min',now()) 
            where unique_query_id= {unique_sql_id} and lock_wait_count>0;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.common.equal_sql_mdg(sql_msg, 'pg_sleep ', '----------', ' ', '(1 row)', '', ' ?column? | ?column? ',
                                  '----------+----------', ' t        | t', '(1 row)')

        text = '---step7:校验lock cont和lock time要么同时为0，' \
               '要么都>0,在l2，l2设置下,expect:成功,(0 rows)'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(30);
                    select * from dbe_perf.get_global_full_sql_by_timestamp\
                        (now()-interval'35 min',now()) 
                    where unique_query_id=(select unique_sql_id 
                        from dbe_perf.statement 
                        where query like \
                            '%on duplicate key update c2 = values(c2)%') 
                            and lock_count<>0 and lock_time=0;
                    select * from dbe_perf.get_global_full_sql_by_timestamp\
                        (now()-interval'35 min', now()) 
                    where lock_count=0 and lock_time<>0;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertTrue(sql_msg.count('(0 rows)') == 2, '执行失败:' + text)

        text = '---step8:校验lock cont和lock_max_count要么同时为0，' \
               '要么都>0,expect:成功,(0 rows)'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(30);
            select * from dbe_perf.get_global_full_sql_by_timestamp\
                (now()-interval'35 min', now()) 
            where lock_count=0 and lock_max_count<>0;
            select * from dbe_perf.get_global_full_sql_by_timestamp\
                (now()-interval'35 min', now()) 
            where lock_count<>0 and lock_max_count=0;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertTrue(sql_msg.count('(0 rows)') == 2, '执行失败:' + text)

        text = '---step9:校验lock_wait_count和lock_wait_time要么同时为0，' \
               '要么都>0,expect:成功,(0 rows)'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(30);
            select * from dbe_perf.get_global_full_sql_by_timestamp\
                (now()-interval'35 min', now()) 
            where lwlock_count=0 and lwlock_time<>0;
            select * from dbe_perf.get_global_full_sql_by_timestamp\
                (now()-interval'35 min',now()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%on duplicate key update c2 = excluded.c2%') 
                    and lwlock_count<>0 and lwlock_time=0;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertTrue(sql_msg.count('(0 rows)') == 2, '执行失败:' + text)

        text = '--step10:校验lock_wait_count和detail的关系，l2模式下,' \
               'expect:成功,(0 rows)'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(30);
            select * from dbe_perf.get_global_full_sql_by_timestamp \
                (now()-interval'35 min', now()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%on duplicate key update c2 = excluded.c2%') 
                    and lwlock_count<>0 and details is null;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertIn('(0 rows)', sql_msg, '执行失败:' + text)

        text = '--step11:查询details is null和lock_count=0,expect:成功,(0 rows)'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(30);
            select * from dbe_perf.get_global_full_sql_by_timestamp \
                (now()-interval'35 min',now()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%on duplicate key update c2 = excluded.c2%') 
                    and details is null and lock_count=0;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertIn('(0 rows)', sql_msg, '执行失败:' + text)

    def tearDown(self):
        text = '---step12: 环境清理,expect:成功:---'
        self.logger.info(text)
        drop_cmd = f'drop table {self.table_name};'
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        self.logger.info(
            '----检查参数，恢复默认配置:rack_stmt_stat_level----')
        check_level_res = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(check_level_res)
        current = check_level_res.splitlines()[-2].strip()
        if self.level_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f"{self.config_level}='{self.level_default}'")
            self.assertTrue(result, '参数恢复失败')
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_level_msg, f'{self.config_level}',
                                  f'{self.level_default}', '(1 row)', flag='1')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0011 finish---')
